package com.rajeshDyadav.file.xml;

import java.io.FileInputStream;
import java.io.IOException;

import java.util.Iterator;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

public class InputExcel02 {
    public static void main(String[] args) throws IOException {
	String xmlFilePath = "/home/ryadav/Desktop/nirajBuild/test/niraj.xml";
	//String inputFilePath = "/home/ryadav/Desktop/nirajBuild/test/raj2.xls";
	String inputFilePath = "/home/ryadav/Downloads/UPE_14thJULY_AllPOWER.xls";
	String outputFilePath = "/home/ryadav/Desktop/nirajBuild/output/niraj.txt";
	
	
	int columnRowNo = 3;

	InputExcel02 inputExcel = new InputExcel02();
	inputExcel.excelOperation(xmlFilePath, inputFilePath, outputFilePath, columnRowNo);
    }

    public String excelOperation(String xmlFilePath, String inputFilePath, String outputFilePath, int columnRowNo) {
	String status = "";
	try {
	    status = "Please Wait!!! This May Take Time..";

	    ArrayList<String> listColumnNames = new ArrayList<String>();
	    ArrayList<String> listColumnValues = new ArrayList<String>();
	    ArrayList<String> listColumnNames2 = new ArrayList<String>();
	    ArrayList<String> listColumnValues2 = new ArrayList<String>();
	    ArrayList<String> listColumnValuesFinal = new ArrayList<String>();

	    XmlParser01 xmlParser01 = new XmlParser01();
	    ArrayList<String> listColumnNamesXml = xmlParser01.getColumnsListFromXml(xmlFilePath);
	    OutputWithText01 outputWithText01 = new OutputWithText01();

	    HSSFWorkbook currentWorkBook = new HSSFWorkbook(new FileInputStream(inputFilePath));
	    HSSFSheet currentSheet = currentWorkBook.getSheetAt(0);

	    HSSFRow currentRow;
	    HSSFCell currentCell;
	    int rowsCount = currentSheet.getPhysicalNumberOfRows();
	    int cellsCount;
	    int i, j, r, c;
	    String currentCellValue;
	    for (r = columnRowNo; r < rowsCount; r++) {
		currentRow = currentSheet.getRow(r);

		if (currentRow != null) {
		    cellsCount = currentSheet.getRow(columnRowNo).getPhysicalNumberOfCells();

		    listColumnValues.clear();
		    listColumnValues2.clear();
		    for (c = 0; c < cellsCount; c++) {
			currentCell = currentRow.getCell(c);
			currentCellValue = " "; // save space where null record
			if (currentCell != null) {
			    switch (currentCell.getCellType()) {
			    case 2:
				currentCellValue = currentCell.getCellFormula();
				break;
			    case 1:
				currentCellValue = currentCell.getStringCellValue();
				break;
			    case 0:
				currentCellValue = String.valueOf(currentCell.getNumericCellValue());
				break;			    
			    default:
				status = "CellType not implemented. Contact Rajesh";
				System.out.println("OMG");
			    }
			}
			if (r == columnRowNo) {
			    listColumnNames.add(currentCellValue);
			} else {
			    listColumnValues.add(currentCellValue);
			}
		    }

		    if (r == columnRowNo) {
			for (i = 0; i < listColumnNamesXml.size(); i++) {
			    for (j = 0; j < listColumnNames.size(); j++) {
				if (listColumnNamesXml.get(i).equalsIgnoreCase(listColumnNames.get(j))) {
				    listColumnNames2.add(listColumnNamesXml.get(i));
				    break;
				}
			    }
			}
			outputWithText01.writeRow(outputFilePath, false, listColumnNamesXml);
		    } else {
			for (i = 0; i < listColumnNamesXml.size(); i++) {
			    for (j = 0; j < listColumnNames.size(); j++) {
				if (listColumnNamesXml.get(i).equalsIgnoreCase(listColumnNames.get(j))) {
				    listColumnValues2.add(listColumnValues.get(j));
				    break;
				}
			    }
			}

			Iterator<String> iterator = listColumnNamesXml.iterator();
			String iteratorData;
			int listIndex = -1;
			listColumnValuesFinal.clear();
			while (iterator.hasNext()) {
			    iteratorData = iterator.next();
			    listIndex = listColumnNames2.indexOf(iteratorData);
			    if (listIndex != -1) {
				listColumnValuesFinal.add(listColumnValues2.get(listIndex));
			    } else {
				listColumnValuesFinal.add("");
			    }
			}
			outputWithText01.writeRow(outputFilePath, true, listColumnValuesFinal);
		    }
		}
	    }
	    status = "OutputFileName: " + outputFilePath;
	} catch (Exception e) {
	    status = "!!! Error Occurred, May Be The Last Line !!!";
	    e.printStackTrace();
	}
	return status;
    }
}